#!/bin/sh

FILE="rules_2012.csv"
EXPORT_FILE="r75.sql"
FW_NAME="R75"

OUT01=$(mktemp /tmp/fwsm.XXXXXXXXXX) || { echo "Failed to create temp file"; exit 1;}

if [ -f $FILE ]; then
        echo "$FILE exist, continu ..."
        echo "Substitution des virgules"
        sed "s/\w\b,/-/g" $FILE >$EXPORT_FILE
        sed -i "s/,/','/g" $EXPORT_FILE

        echo "Suppression des colonnes"
        awk -F';' '{print "R75\047,\047"$12"\047,\047\047,\047\047,\047\047,\047"$13"\047,\047\047,\047"$50"\047,\047\047,\047"$31"\047,\047"$44"\047,\047"$59"\047,\047\047,\047"$61"\047,\047"$27"\047"}' $EXPORT_FILE > $OUT01

        echo "Suppression des apostrophes"
        sed -i "s/ \w\b'/ /g" $OUT01 
        sed -i "s/'personna non grata'/personna non grata/g" $OUT01
        sed -i "s/'SASSER'/SASSER/g" $OUT01

        echo "Ajout en début de ligne"
        sed -i "s/^/('','/" $OUT01

        echo "Ajout en fin de ligne"
        sed -i "s/$/),/" $OUT01

        echo "Suppression des espaces"
        sed -i "s/ ','',/','',/g" $OUT01
        sed -i "s/,' ', /,'',/g" $OUT01

#       echo "Suppression des apostrophes"
#       sed -i "s/d'acces/d acces/" $OUT01

        echo "Suppression de la première ligne"
        sed -i '1d' $OUT01
        sed -i '1d' $OUT01
        sed -i '1d' $OUT01

        echo "ajout de l'entête"
        sed -i 1i'INSERT INTO `acl` (`id`, `fw_name`, `rule_num`, `name`, `line`, `type`, `function`, `protocol`, `src_net`, `src_port`, `dst_net`, `dst_port`, `time`, `inactive`, `log`, `remark`) VALUES' $OUT01


        echo "Remplacement de la dernière virgule par un point virgule"
        sed -i "$ s/,$/;/" $OUT01

        echo "Préparation du fichier"
        mv $OUT01 $EXPORT_FILE


        echo "Done !"

else
echo "'$FILE' not found !"
fi